using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using fuzhu;
using LKU8.shoukuan;
using System.ComponentModel;
using System.Text.RegularExpressions;
using NPOI.SS.Util;

namespace fuzhu
{
    /// <summary>
    /// 万吉制造订单Excel导入助手
    /// 专门处理万吉制造订单Excel模板的导入
    /// </summary>
    public class WanJiExcelImportHelper_JH
    {
        private static WriteLog wl = new WriteLog();

        
         

        /// <summary>
        /// 解析标题行，建立列名到列索引的映射
        /// </summary>
        /// <param name="headerRow">标题行</param>
        /// <returns>列名到列索引的映射字典</returns>
        private static Dictionary<string, int> ParseHeaderRow(IRow headerRow)
        {
            Dictionary<string, int> columnMapping = new Dictionary<string, int>();
            
            for (int i = 0; i <= headerRow.LastCellNum; i++)
            {
                ICell cell = headerRow.GetCell(i);
                if (cell != null)
                {
                    string cellValue = cell.ToString().Trim();
                    if (!string.IsNullOrEmpty(cellValue))
                    {
                        // 处理可能包含括号的列名，如"订单批量（Pcs）"
                        columnMapping[cellValue] = i;
                        
                        // 为了兼容性，也添加去掉括号内容的版本
                        string simplifiedName = Regex.Replace(cellValue, @"[（(].*?[）)]", "").Trim();
                        if (!columnMapping.ContainsKey(simplifiedName) && simplifiedName != cellValue)
                        {
                            columnMapping[simplifiedName] = i;
                        }
                    }
                }
            }

            return columnMapping;
        }

         

        /// <summary>
        /// 从行中获取指定列的值
        /// </summary>
        /// <param name="row">数据行</param>
        /// <param name="columnMapping">列名映射</param>
        /// <param name="columnName">列名</param>
        /// <returns>单元格值</returns>
        private static string GetCellValue(IRow row, Dictionary<string, int> columnMapping, string columnName)
        {
            if (!columnMapping.ContainsKey(columnName))
            {
                return "";
            }

            int columnIndex = columnMapping[columnName];
            ICell cell = row.GetCell(columnIndex);
            
            if (cell == null)
            {
                return "";
            }

            try
            {
                switch (cell.CellType)
                {
                    case CellType.STRING:
                        return cell.StringCellValue.Trim();
                    case CellType.NUMERIC:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            return cell.DateCellValue.ToString("yyyy-MM-dd");
                        }
                        else
                        {
                            return cell.NumericCellValue.ToString();
                        }
                    case CellType.BOOLEAN:
                        return cell.BooleanCellValue.ToString();
                    case CellType.FORMULA:
                        // 改进公式处理逻辑
                        return GetFormulaValue(cell);
                    case CellType.BLANK:
                        return "";
                    case CellType.ERROR:
                        wl.WriteLogs(string.Format("单元格错误: {0}",cell.ErrorCellValue));
                        return "";
                    default:
                        return "";
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs(string.Format("获取单元格值错误: {0}",ex.Message));
                return "";
            }
        }

        /// <summary>
        /// 获取公式单元格的值
        /// </summary>
        /// <param name="cell">公式单元格</param>
        /// <returns>公式计算结果</returns>
        private static string GetFormulaValue(ICell cell)
        {
            try
            {
                // 尝试获取公式的计算结果
                IFormulaEvaluator evaluator = cell.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
                CellValue cellValue = evaluator.Evaluate(cell);
                
                switch (cellValue.CellType)
                {
                    case CellType.STRING:
                        return cellValue.StringValue.Trim();
                    case CellType.NUMERIC:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            return DateUtil.GetJavaDate(cellValue.NumberValue).ToString("yyyy-MM-dd");
                        }
                        else
                        {
                            return cellValue.NumberValue.ToString();
                        }
                    case CellType.BOOLEAN:
                        return cellValue.BooleanValue.ToString();
                    case CellType.ERROR:
                        wl.WriteLogs(string.Format("公式计算错误: {0}",cellValue.ErrorValue));
                        return "";
                    default:
                        return "";
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs(string.Format("公式计算异常: {0}",ex.Message));
                
                // 如果公式计算失败，尝试获取缓存结果
                try
                {
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.STRING:
                            return cell.StringCellValue.Trim();
                        case CellType.NUMERIC:
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                return cell.DateCellValue.ToString("yyyy-MM-dd");
                            }
                            else
                            {
                                return cell.NumericCellValue.ToString();
                            }
                        case CellType.BOOLEAN:
                            return cell.BooleanCellValue.ToString();
                        default:
                            return "";
                    }
                }
                catch
                {
                    wl.WriteLogs("无法获取公式结果，返回空值");
                    return "";
                }
            }
        }

      
        /// <summary>
        /// 获取Excel文件中所有工作表的名称
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>工作表名称列表</returns>
        public static List<string> GetSheetNames(string filePath)
        {
            List<string> sheetNames = new List<string>();
            
            try
            {
                IWorkbook workbook;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(file);
                }

                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    sheetNames.Add(workbook.GetSheetName(i));
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs("获取工作表名称时发生错误: " + ex.Message);
                throw;
            }

            return sheetNames;
        }

        /// <summary>
        /// 从新模板格式的Excel文件导入生产计划数据
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <param name="sheetIndex">工作表索引</param>
        /// <param name="dateLoc">日期单元格位置，如"I3"</param>
        /// <param name="banciLoc">班次单元格位置，如"J3"</param>
        /// <param name="headLoc">标题单元格位置，如"E2"</param>
        /// <returns>导入的生产计划数据列表</returns>
        public static BindingList<WanJi_SCJH_View> ImportSCJHFromExcel(string filePath, int sheetIndex, string dateLoc, string banciLoc, string headLoc)
        {
            BindingList<WanJi_SCJH_View> resultList = new BindingList<WanJi_SCJH_View>();
            
            try
            {
                IWorkbook workbook;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(file);
                }

                if (sheetIndex >= workbook.NumberOfSheets)
                {
                    throw new Exception(string.Format("工作表索引 {0} 超出范围，文件只有 {1} 个工作表", sheetIndex, workbook.NumberOfSheets));
                }

                ISheet sheet = workbook.GetSheetAt(sheetIndex);
                
                if (sheet == null)
                {
                    throw new Exception("Excel文件中没有找到工作表");
                }

                // 获取固定单元格的值
                DateTime dDate = GetCellDateValue(sheet, dateLoc);
                string cBanci = GetCellStringValue(sheet, banciLoc);
                string cGongxu = GetProcessFromTitle(sheet, headLoc);

                // 获取标题行（第四行，索引为3）
                IRow headerRow = sheet.GetRow(3);
                if (headerRow == null)
                {
                    throw new Exception("Excel文件中没有找到标题行（第四行）");
                }

                // 解析标题行，建立列名到列索引的映射
                Dictionary<string, int> columnMapping = ParseSCJHHeaderRow(headerRow);

                // 验证必需的列是否存在
                ValidateSCJHRequiredColumns(columnMapping);

                // 从第五行开始读取数据（索引为4）
                int rowIndex = 4;

                while (rowIndex <= sheet.LastRowNum)
                {
                    IRow row = sheet.GetRow(rowIndex);
                    if (row == null)
                    {
                        rowIndex++;
                        continue;
                    }

                    // 检查工单号是否为空，如果为空则停止读取
                    string moCode = GetMergedCellValue(sheet, row, columnMapping, "工单号");
                    if (string.IsNullOrEmpty(moCode))
                    {
                        break; // 工单号为空，停止读取
                    }

                    // 创建生产计划视图对象
                    WanJi_SCJH_View scjhView = new WanJi_SCJH_View();
                    
                    // 设置固定值
                    scjhView.dDate = dDate;
                    scjhView.cBanci = cBanci;
                    scjhView.cGongxu = cGongxu;
                    
                    // 填充数据
                    FillSCJHOrderData(scjhView, sheet, row, columnMapping);
                    
                    resultList.Add(scjhView);
                    
                    rowIndex++;
                }

                return resultList;
            }
            catch (Exception ex)
            {
                wl.WriteLogs("导入Excel时发生错误: " + ex.Message);
                throw new Exception("导入Excel文件失败：" + ex.Message, ex);
            }
        }

        /// <summary>
        /// 从标题获取工序信息
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="headLoc">标题位置</param>
        /// <returns>工序信息</returns>
        private static string GetProcessFromTitle(ISheet sheet, string headLoc)
        {
            try
            {
                CellReference cellRef = new CellReference(headLoc);
                IRow row = sheet.GetRow(cellRef.Row);
                if (row != null)
                {
                    ICell cell = row.GetCell(cellRef.Col);
                    if (cell != null)
                    {
                        string titleValue = cell.ToString().Trim();
                        if (titleValue.Contains("SMT"))
                        {
                            return "SMT";
                        }
                        else if (titleValue.Contains("DIP"))
                        {
                            return "DIP";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs("获取工序信息时发生错误: " + ex.Message);
            }
            return "";
        }

        /// <summary>
        /// 获取指定位置单元格的日期值
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="cellLoc">单元格位置，如"I3"</param>
        /// <returns>日期值</returns>
        private static DateTime GetCellDateValue(ISheet sheet, string cellLoc)
        {
            try
            {
                CellReference cellRef = new CellReference(cellLoc);
                IRow row = sheet.GetRow(cellRef.Row);
                if (row != null)
                {
                    ICell cell = row.GetCell(cellRef.Col);
                    if (cell != null)
                    {
                        if (cell.CellType == CellType.NUMERIC && DateUtil.IsCellDateFormatted(cell))
                        {
                            return cell.DateCellValue;
                        }
                        else if (cell.CellType == CellType.STRING)
                        {
                            DateTime parsedDate;
                            if (DateTime.TryParse(cell.StringCellValue, out parsedDate))
                            {
                                return parsedDate;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs("获取日期值时发生错误: " + ex.Message);
            }
            return DateTime.Now; // 默认返回当前日期
        }

        /// <summary>
        /// 获取指定位置单元格的字符串值
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="cellLoc">单元格位置，如"J3"</param>
        /// <returns>字符串值</returns>
        private static string GetCellStringValue(ISheet sheet, string cellLoc)
        {
            try
            {
                CellReference cellRef = new CellReference(cellLoc);
                IRow row = sheet.GetRow(cellRef.Row);
                if (row != null)
                {
                    ICell cell = row.GetCell(cellRef.Col);
                    if (cell != null)
                    {
                        return cell.ToString().Trim();
                    }
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs("获取字符串值时发生错误: " + ex.Message);
            }
            return "";
        }

        /// <summary>
        /// 解析生产计划标题行，建立列名到列索引的映射
        /// </summary>
        /// <param name="headerRow">标题行</param>
        /// <returns>列名到列索引的映射字典</returns>
        private static Dictionary<string, int> ParseSCJHHeaderRow(IRow headerRow)
        {
            Dictionary<string, int> columnMapping = new Dictionary<string, int>();
            
            for (int i = 0; i <= headerRow.LastCellNum; i++)
            {
                ICell cell = headerRow.GetCell(i);
                if (cell != null)
                {
                    string cellValue = cell.ToString().Trim();
                    if (!string.IsNullOrEmpty(cellValue))
                    {
                        // 原始值直接映射
                        columnMapping[cellValue] = i;
                        
                        // 清理后的字段名：删除空格，取括号前的内容
                        string cleanedValue = cellValue.Replace(" ", ""); // 删除所有空格
                        
                        // 如果有括号，取括号前面的内容
                        int bracketIndex = cleanedValue.IndexOfAny(new char[] { '（', '(' });
                        if (bracketIndex > 0)
                        {
                            cleanedValue = cleanedValue.Substring(0, bracketIndex);
                        }
                        
                        // 清理后的字段名也进行映射（如果与原始值不同）
                        if (cleanedValue != cellValue && !columnMapping.ContainsKey(cleanedValue))
                        {
                            columnMapping[cleanedValue] = i;
                        }
                        
                        // 处理可能的别名映射
                        switch (cleanedValue)
                        {
                            case "工单号":
                                columnMapping["cMocode"] = i;
                                break;
                            case "线别":
                                columnMapping["cXianbie"] = i;
                                break;
                            case "生产计划数":
                                columnMapping["iQuantity"] = i;
                                break;
                        }
                    }
                }
            }

            return columnMapping;
        }
        
        /// <summary>
        /// 验证生产计划必需的列是否存在
        /// </summary>
        /// <param name="columnMapping">列名映射</param>
        private static void ValidateSCJHRequiredColumns(Dictionary<string, int> columnMapping)
        {
            string[] requiredColumns = { "工单号", "线别", "生产计划数" };
            List<string> missingColumns = new List<string>();

            foreach (string required in requiredColumns)
            {
                if (!columnMapping.ContainsKey(required))
                {
                    missingColumns.Add(required);
                }
            }

            if (missingColumns.Count > 0)
            {
                throw new Exception("Excel模板缺少必需的列：" + string.Join(", ", missingColumns.ToArray()));
            }
        }

        /// <summary>
        /// 获取合并单元格的值（支持合并单元格）
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="row">数据行</param>
        /// <param name="columnMapping">列名映射</param>
        /// <param name="columnName">列名</param>
        /// <returns>单元格值</returns>
        private static string GetMergedCellValue(ISheet sheet, IRow row, Dictionary<string, int> columnMapping, string columnName)
        {
            if (!columnMapping.ContainsKey(columnName))
            {
                return "";
            }

            int columnIndex = columnMapping[columnName];
            ICell cell = row.GetCell(columnIndex);
            
            if (cell == null)
            {
                return "";
            }

            // 检查是否是合并单元格
            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                CellRangeAddress mergedRegion = sheet.GetMergedRegion(i);
                if (mergedRegion.IsInRange(row.RowNum, columnIndex))
                {
                    // 获取合并单元格的第一个单元格值
                    IRow firstRow = sheet.GetRow(mergedRegion.FirstRow);
                    if (firstRow != null)
                    {
                        ICell firstCell = firstRow.GetCell(mergedRegion.FirstColumn);
                        if (firstCell != null)
                        {
                            return GetCellValueAsString(firstCell);
                        }
                    }
                }
            }

            // 如果不是合并单元格，直接获取值
            return GetCellValueAsString(cell);
        }

        /// <summary>
        /// 获取单元格的字符串值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>字符串值</returns>
        private static string GetCellValueAsString(ICell cell)
        {
            if (cell == null)
            {
                return "";
            }

            try
            {
                switch (cell.CellType)
                {
                    case CellType.STRING:
                        return cell.StringCellValue.Trim();
                    case CellType.NUMERIC:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            return cell.DateCellValue.ToString("yyyy-MM-dd");
                        }
                        else
                        {
                            return cell.NumericCellValue.ToString();
                        }
                    case CellType.BOOLEAN:
                        return cell.BooleanCellValue.ToString();
                    case CellType.FORMULA:
                        return GetFormulaValue(cell);
                    case CellType.BLANK:
                        return "";
                    case CellType.ERROR:
                        wl.WriteLogs(string.Format("单元格错误: {0}", cell.ErrorCellValue));
                        return "";
                    default:
                        return "";
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs(string.Format("获取单元格值错误: {0}", ex.Message));
                return "";
            }
        }

        /// <summary>
        /// 填充生产计划订单数据
        /// </summary>
        /// <param name="scjhView">生产计划视图对象</param>
        /// <param name="sheet">工作表</param>
        /// <param name="row">数据行</param>
        /// <param name="columnMapping">列名映射</param>
        private static void FillSCJHOrderData(WanJi_SCJH_View scjhView, ISheet sheet, IRow row, Dictionary<string, int> columnMapping)
        {
            try
            {
                string cRowNoValue = GetCellValue(row, columnMapping, "序号");
                if (!string.IsNullOrEmpty(cRowNoValue))
                {
                    // 去掉可能的非数字字符
                    string numericValue = Regex.Replace(cRowNoValue, @"[^\d.]", "");
                    int parsedQty;

                    if (int.TryParse(numericValue, out parsedQty))
                    {
                        scjhView.cRowNo = parsedQty;
                    }
                    else
                    {
                        scjhView.cRowNo = 0;
                    }
                }
                else
                {
                    scjhView.cRowNo = 0;
                }
                // 工单号
                scjhView.cMocode = GetMergedCellValue(sheet, row, columnMapping, "工单号");
                
                // 线别
                scjhView.cXianbie = GetMergedCellValue(sheet, row, columnMapping, "线别");
                
                // 生产计划数
                string quantityValue = GetMergedCellValue(sheet, row, columnMapping, "生产计划数");
                if (!string.IsNullOrEmpty(quantityValue))
                {
                    decimal parsedQty;
                    if (decimal.TryParse(quantityValue, out parsedQty))
                    {
                        scjhView.iQuantity = parsedQty;
                    }
                    else
                    {
                        scjhView.iQuantity = 0;
                    }
                }
                else
                {
                    scjhView.iQuantity = 0;
                }

                // 其他可能的列
                if (columnMapping.ContainsKey("机种名"))
                {
                    scjhView.cInvname = GetMergedCellValue(sheet, row, columnMapping, "机种名");
                }
                
                if (columnMapping.ContainsKey("U8编码"))
                {
                    scjhView.cInvcode = GetMergedCellValue(sheet, row, columnMapping, "U8编码");
                }
                
                if (columnMapping.ContainsKey("批量"))
                {
                    string batchValue = GetMergedCellValue(sheet, row, columnMapping, "批量");
                    if (!string.IsNullOrEmpty(batchValue))
                    {
                        decimal parsedBatch;
                        if (decimal.TryParse(batchValue, out parsedBatch))
                        {
                            scjhView.iMoquantity = parsedBatch;
                        }
                    }
                }
                
                // 处理备注字段（可能包含空格）
                if (columnMapping.ContainsKey("备注"))
                {
                    scjhView.cMemo = GetMergedCellValue(sheet, row, columnMapping, "备注");
                }
                else if (columnMapping.ContainsKey("备 注")) // 带空格的情况
                {
                    scjhView.cMemo = GetMergedCellValue(sheet, row, columnMapping, "备 注");
                }

                // 处理周期字段（可能包含括号）
                if (columnMapping.ContainsKey("周期"))
                {
                    string zhouqiValue = GetMergedCellValue(sheet, row, columnMapping, "周期");
                    if (!string.IsNullOrEmpty(zhouqiValue))
                    {
                        decimal parsedZhouqi;
                        if (decimal.TryParse(zhouqiValue, out parsedZhouqi))
                        {
                            scjhView.dzhouqi = parsedZhouqi;
                        }
                    }
                }
                else if (columnMapping.ContainsKey("周期(秒)"))
                {
                    string zhouqiValue = GetMergedCellValue(sheet, row, columnMapping, "周期(秒)");
                    if (!string.IsNullOrEmpty(zhouqiValue))
                    {
                        decimal parsedZhouqi;
                        if (decimal.TryParse(zhouqiValue, out parsedZhouqi))
                        {
                            scjhView.dzhouqi = parsedZhouqi;
                        }
                    }
                }
                
                // 处理预定换线时间字段（可能包含括号）
                if (columnMapping.ContainsKey("预定换线时间"))
                {
                    string huanxianValue = GetMergedCellValue(sheet, row, columnMapping, "预定换线时间");
                    if (!string.IsNullOrEmpty(huanxianValue))
                    {
                        decimal parsedHuanxian;
                        if (decimal.TryParse(huanxianValue, out parsedHuanxian))
                        {
                            scjhView.dHuanxian = parsedHuanxian;
                        }
                    }
                }
                else if (columnMapping.ContainsKey("预定换线时间（H)"))
                {
                    string huanxianValue = GetMergedCellValue(sheet, row, columnMapping, "预定换线时间（H)");
                    if (!string.IsNullOrEmpty(huanxianValue))
                    {
                        decimal parsedHuanxian;
                        if (decimal.TryParse(huanxianValue, out parsedHuanxian))
                        {
                            scjhView.dHuanxian = parsedHuanxian;
                        }
                    }
                }
                
                // 初始化默认值
                scjhView.chk = false;
                scjhView.bU8 = false;
                scjhView.cImportResult = "";
            }
            catch (Exception ex)
            {
                wl.WriteLogs("填充生产计划数据时发生错误: " + ex.Message);
                throw;
            }
        }
    }
}